package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.WorkDAO;
import com.core.database.Manager;
import com.core.entities.Work;
import com.core.exception.DAOException;

public class WorkDAOImpl implements WorkDAO {
	Work work = new Work();
	Connection connection = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	ArrayList<Work> workList = new ArrayList<Work>();

	private static WorkDAOImpl instance = null;

	public static WorkDAOImpl getInstance() {
		if (instance == null) {
			instance = new WorkDAOImpl();
		}
		return instance;
	}

	@Override
	public void insert(Work o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "INSERT INTO work (compny_name, land_phone, mobile_phone,"
					+ "email, website, online_account_id, city_id) VALUE (?, ?, ?, ?, ?, ?, ?)";
			ps = connection.prepareStatement(sql);
			ps.setString(1, o.getCompny_name());
			ps.setString(2, o.getLand_phone());
			ps.setString(3, o.getMobile_phone());
			ps.setString(4, o.getEmail());
			ps.setString(5, o.getWebsite());
			ps.setInt(6, o.getOnline_account_id());
			ps.setInt(7, o.getCity_id());

			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("No row inserted in Work table!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("cannot add new Work", ex);
		} finally {
			Manager.closeConnection(connection);
		}

	}

	@Override
	public Work findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE id = '" + id + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public List<Work> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				work.setId(rs.getInt(1));
				work.setCompny_name(rs.getString(2));
				work.setLand_phone(rs.getString(3));
				work.setMobile_phone(rs.getString(4));
				work.setEmail(rs.getString(5));
				work.setWebsite(rs.getString(6));
				work.setOnline_account_id(rs.getInt(7));
				work.setCity_id(rs.getInt(8));

				workList.add(work);

			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return workList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM work WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, work.getCompny_name());
				ps.setString(2, work.getLand_phone());
				ps.setString(3, work.getMobile_phone());
				ps.setString(4, work.getEmail());
				ps.setString(5, work.getWebsite());
				ps.setInt(6, work.getOnline_account_id());
				ps.setInt(7, work.getCity_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}

	}

	@Override
	public void update(Work o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "UPDATE work SET compny_name'" + o.getCompny_name()
					+ "', land_phone = '" + o.getLand_phone()
					+ "', mobile_phone ='" + o.getMobile_phone()
					+ "', email ='" + o.getEmail() + "', website = '"
					+ o.getWebsite() + "', online_account_id = '"
					+ o.getOnline_account_id() + "', city_id ='"
					+ o.getCity_id() + "' WHERE id = " + o.getId();
			ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, o.getCompny_name());
				ps.setString(2, o.getLand_phone());
				ps.setString(3, o.getMobile_phone());
				ps.setString(4, o.getEmail());
				ps.setString(5, o.getWebsite());
				ps.setInt(6, o.getOnline_account_id());
				ps.setInt(7, o.getCity_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}

	}

	@Override
	public Work findByCompny_name(String compny_name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE company_name = '"
					+ compny_name + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public Work findByLand_phone(String land_phone) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE land_phone = '" + land_phone
					+ "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public Work findByMobile_phone(String mobile_phone) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE mobile_phone = '"
					+ mobile_phone + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public Work findByEmail(String email) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE email = '" + email + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public Work findByWebsite(String website) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE website = '" + website + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return work;
	}

	@Override
	public List<Work> findByOnline_account_id(int online_account_id) {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE online_account_id = '"
					+ online_account_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

					workList.add(work);

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return workList;
	}

	@Override
	public List<Work> findByCity_id(int city_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM work WHERE city_id = '" + city_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					work.setId(rs.getInt(1));
					work.setCompny_name(rs.getString(2));
					work.setLand_phone(rs.getString(3));
					work.setMobile_phone(rs.getString(4));
					work.setEmail(rs.getString(5));
					work.setWebsite(rs.getString(6));
					work.setOnline_account_id(rs.getInt(7));
					work.setCity_id(rs.getInt(8));

					workList.add(work);

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return workList;
	}

}
